import configparser
import os
import requests
from langchain.prompts import PromptTemplate
import json
import pandas as pd
from pyecharts import options as opts
from pyecharts.charts import Bar, Pie, Line, Scatter, Radar, Funnel
from pyecharts.globals import ThemeType
import re
from typing import List, Dict, Any, Tuple

# # 导入LLM
# from boat_nl2sql.llm_module import SiliconFlow
# llm = SiliconFlow()

# 获取当前脚本所在目录
current_dir = os.path.dirname(os.path.abspath(__file__))
# 构建配置文件的绝对路径（假设 config.ini 在项目根目录）
config_path = os.path.join(current_dir, '..', 'config.ini')
config = configparser.ConfigParser()
config.read(config_path)

# 获取配置值
API_URL = config.get('API', 'URL')
MODEL_NAME = config.get('API', 'MODEL_NAME')

def convert_sql_result_to_dataframe(result_list):
    """
    将元组列表转换为pandas DataFrame

    参数:
        result_list: 包含位置名称和数值的元组列表

    返回:
        pandas DataFrame
    """

    # 确保输入是列表
    if not isinstance(result_list, list):
        print("输入不是列表类型")
        return pd.DataFrame()

        # 确保列表不为空
    if not result_list:
        print("输入列表为空")
        return pd.DataFrame()

    try:
        # 直接创建DataFrame，指定列名
        df = pd.DataFrame(result_list, columns=["位置", "数量"])

        # 确保数量列是数值类型
        df["数量"] = pd.to_numeric(df["数量"])

        return df

    except Exception as e:
        # print(f"转换数据时出错: {e}")
        # 尝试更通用的方法
        try:
            # 动态确定列数
            columns = [f"列{i + 1}" for i in range(len(result_list[0]))]
            return pd.DataFrame(result_list, columns=columns)
        except:
            # 返回空DataFrame
            return pd.DataFrame()

def analyze_data_for_visualization(df: pd.DataFrame, query: str, question: str) -> Dict[str, Any]:
    """
    使用大模型分析数据并确定最合适的可视化类型

    参数:
        df: 包含SQL查询结果的pandas DataFrame
        query: 执行的SQL查询
        question: 原始问题

    返回:
        包含可视化推荐信息的字典
    """
    # 准备数据摘要
    num_rows, num_cols = df.shape
    column_types = {col: str(df[col].dtype) for col in df.columns}
    data_sample = df.head(5).to_dict(orient='records')

    # 创建提示
    prompt = f"""作为数据可视化专家，请分析以下SQL查询结果，并推荐最合适的ECharts图表类型。  

原始问题: {question}  
执行的SQL查询: {query}  

数据摘要:  
- 行数: {num_rows}  
- 列数: {num_cols}  
- 列名及类型: {json.dumps(column_types, ensure_ascii=False)}  
- 数据示例(前5行): {json.dumps(data_sample, ensure_ascii=False)}  

请提供以下JSON格式的回答:  
{{  
  "chart_type": "图表类型(bar/pie/line/scatter/radar/funnel)",  
  "title": "建议的图表标题",  
  "x_axis": "建议用于X轴的列名(对于饼图使用label)",  
  "y_axis": "建议用于Y轴的列名(对于饼图使用value)",  
  "series_name": "数据系列名称",  
  "explanation": "为什么选择这种图表类型的简短解释",  
  "additional_settings": {{选填的额外配置}}  
}}  

你的回答应该是一个有效的JSON对象，不要包含额外的文本或代码块标记。  
"""

    # # 调用大模型获取推荐
    # response = llm.invoke(prompt)
    data = {
        "model": MODEL_NAME,
        "messages": [
            {"role": "system", "content": "你是一个专业的数据可视化专家，请分析以下SQL查询结果，并推荐最合适的ECharts图表类型"},
            {"role": "user", "content": prompt}
        ],
        "temperature": 0.1,
        "presence_penalty": 0.4,
        "frequency_penalty": 0.7,
        "stream": False
    }

    response = requests.post(
        API_URL,
        data=json.dumps(data),
        stream=False,
        headers={'Content-Type': 'application/json'}
    )
    response.raise_for_status()

    for chunk in response.iter_lines():
        if chunk:
            decoded = json.loads(chunk.decode('utf-8'))
            if 'message' in decoded and 'content' in decoded['message']:
                content = decoded['message']['content']

    # 尝试解析JSON响应
    try:
        # 提取JSON部分
        json_match = re.search(r'\{.*\}', content, re.DOTALL)
        if json_match:
            clean_json = json_match.group(0)
            recommendation = json.loads(clean_json)
        else:
            # 如果没有找到JSON格式，使用默认设置
            recommendation = {
                "chart_type": "bar",
                "title": question,
                "x_axis": df.columns[0],
                "y_axis": df.columns[1] if len(df.columns) > 1 else df.columns[0],
                "series_name": "数据",
                "explanation": "未能提取有效的JSON推荐，使用默认柱状图。"
            }
    except Exception as e:
        print(f"解析推荐时出错: {e}")
        # 使用默认设置
        recommendation = {
            "chart_type": "bar",
            "title": question,
            "x_axis": df.columns[0],
            "y_axis": df.columns[1] if len(df.columns) > 1 else df.columns[0],
            "series_name": "数据",
            "explanation": f"解析JSON失败，使用默认设置: {str(e)}"
        }

    return recommendation


def create_chart(df: pd.DataFrame, recommendation: Dict[str, Any]):
    """
    根据推荐创建ECharts图表

    参数:
        df: 包含SQL查询结果的pandas DataFrame
        recommendation: 包含可视化推荐的字典

    返回:
        创建的ECharts图表对象
    """
    chart_type = recommendation.get("chart_type", "bar").lower()
    title = recommendation.get("title", "数据可视化")
    x_axis = recommendation.get("x_axis", df.columns[0])
    y_axis = recommendation.get("y_axis", df.columns[1] if len(df.columns) > 1 else df.columns[0])
    series_name = recommendation.get("series_name", "数据")

    # 确保x_axis和y_axis存在于DataFrame中
    if x_axis not in df.columns:
        x_axis = df.columns[0]
    if y_axis not in df.columns:
        y_axis = df.columns[1] if len(df.columns) > 1 else df.columns[0]

        # 根据图表类型创建相应的图表
    if chart_type == "bar":
        chart = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT,width="850px",height="570px"))
            .add_xaxis(df[x_axis].tolist())
            .add_yaxis(series_name, df[y_axis].tolist())
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45)),
                datazoom_opts=[opts.DataZoomOpts(range_start=0, range_end=60)],
                toolbox_opts=opts.ToolboxOpts(),
                tooltip_opts=opts.TooltipOpts(trigger="axis"),
            )
        )

    elif chart_type == "pie":
        data_pairs = [list(z) for z in zip(df[x_axis].tolist(), df[y_axis].tolist())]
        chart = (
            Pie(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add(
                series_name,
                data_pairs,
                radius=["30%", "75%"],
                label_opts=opts.LabelOpts(formatter="{b}: {c} ({d}%)"),
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
                toolbox_opts=opts.ToolboxOpts(),
            )
        )

    elif chart_type == "line":
        chart = (
            Line(init_opts=opts.InitOpts(theme=ThemeType.LIGHT,width="900px",height="600px"))
            .add_xaxis(df[x_axis].tolist())
            .add_yaxis(
                series_name,
                df[y_axis].tolist(),
                markpoint_opts=opts.MarkPointOpts(
                    data=[
                        opts.MarkPointItem(type_="max", name="最大值"),
                        opts.MarkPointItem(type_="min", name="最小值"),
                    ]
                ),
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45)),
                datazoom_opts=[opts.DataZoomOpts()],
                toolbox_opts=opts.ToolboxOpts(),
                tooltip_opts=opts.TooltipOpts(trigger="axis"),
            )
        )

    elif chart_type == "scatter":
        chart = (
            Scatter(init_opts=opts.InitOpts(theme=ThemeType.LIGHT,width="900px",height="600px"))
            .add_xaxis(df[x_axis].tolist())
            .add_yaxis(series_name, df[y_axis].tolist())
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                xaxis_opts=opts.AxisOpts(
                    type_="value",
                    splitline_opts=opts.SplitLineOpts(is_show=True)
                ),
                yaxis_opts=opts.AxisOpts(
                    type_="value",
                    splitline_opts=opts.SplitLineOpts(is_show=True)
                ),
                visualmap_opts=opts.VisualMapOpts(type_="size", min_=20, max_=80),
                toolbox_opts=opts.ToolboxOpts(),
            )
        )

    elif chart_type == "funnel":
        data_pairs = [list(z) for z in zip(df[x_axis].tolist(), df[y_axis].tolist())]
        chart = (
            Funnel(init_opts=opts.InitOpts(theme=ThemeType.LIGHT,width="900px",height="600px"))
            .add(
                series_name,
                data_pairs,
                label_opts=opts.LabelOpts(formatter="{b}: {c}"),
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
                toolbox_opts=opts.ToolboxOpts(),
            )
        )

    elif chart_type == "radar":
        # 雷达图需要特殊处理
        indicators = [{"name": str(name), "max": df[y_axis].max()} for name in df[x_axis]]
        data = [[float(val) for val in df[y_axis].tolist()]]

        chart = (
            Radar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT,width="900px",height="600px"))
            .add_schema(
                schema=indicators,
                shape="circle",
            )
            .add(series_name, data)
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                toolbox_opts=opts.ToolboxOpts(),
            )
        )

    else:
        # 默认使用柱状图
        chart = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT,width="900px",height="600px"))
            .add_xaxis(df[x_axis].tolist())
            .add_yaxis(series_name, df[y_axis].tolist())
            .set_global_opts(
                title_opts=opts.TitleOpts(title=title),
                toolbox_opts=opts.ToolboxOpts(),
            )
        )

    return chart


def visualize_sql_result(sql_result: str, query: str, question: str, output_file: str = None):
    """
    可视化SQL查询结果

    参数:
        sql_result: SQL查询结果字符串
        query: 执行的SQL查询
        question: 原始问题
        output_file: 输出HTML文件路径，如果为None则使用"sql_visualization.html"

    返回:
        生成的HTML文件路径
    """

    # 转换SQL结果为DataFrame
    df = convert_sql_result_to_dataframe(sql_result)
    print(df)

    if df.empty:
        print("SQL结果为空或格式无法解析，无法生成可视化。")
        return None

        # 分析数据并获取可视化推荐
    recommendation = analyze_data_for_visualization(df, query, question)
    print(f"可视化推荐: {json.dumps(recommendation, ensure_ascii=False, indent=2)}")

    # 创建图表
    chart = create_chart(df, recommendation)

    # 保存HTML文件
    if output_file is None:
        output_file = "sql_visualization.html"

    chart.render(output_file)
    print(f"可视化已保存至: {output_file}")

    return output_file


# 主函数示例用法
# if __name__ == "__main__":
    # question = '分析一下2025年1月油墩港(东大盈船闸)的流量组成。'
    # sql_result = [(1, 45, 20, 25, '44.44%', '55.56%'), (2, 54, 23, 31, '42.59%', '57.41%'), (3, 93, 54, 39, '58.06%', '41.94%'), (4, 68, 38, 30, '55.88%', '44.12%'), (5, 65, 35, 30, '53.85%', '46.15%'), (6, 120, 57, 63, '47.5%', '52.5%'), (7, 113, 72, 41, '63.72%', '36.28%'), (8, 57, 31, 26, '54.39%', '45.61%'), (9, 50, 29, 21, '58%', '42%'), (10, 55, 29, 26, '52.73%', '47.27%'), (11, 66, 28, 38, '42.42%', '57.58%'), (12, 54, 25, 29, '46.3%', '53.7%'), (13, 62, 28, 34, '45.16%', '54.84%'), (14, 73, 35, 38, '47.95%', '52.05%'), (15, 59, 28, 31, '47.46%', '52.54%'), (16, 44, 18, 26, '40.91%', '59.09%'), (17, 69, 38, 31, '55.07%', '44.93%'), (18, 56, 24, 32, '42.86%', '57.14%'), (19, 45, 19, 26, '42.22%', '57.78%'), (20, 21, 8, 13, '38.1%', '61.9%'), (21, 28, 9, 19, '32.14%', '67.86%'), (22, 14, 12, 2, '85.71%', '14.29%'), (23, 6, 3, 3, '50%', '50%'), (24, 4, 1, 3, '25%', '75%'), (25, 4, 0, 4, '0%', '100%'), (26, 2, 1, 1, '50%', '50%'), (28, 1, 1, 0, '100%', '0%'), (31, 1, 0, 1, '0%', '100%')]
    # generated_sql = '''SELECT EXTRACT(DAY FROM da.PASSTIME) AS DAY_OF_MONTH, COUNT(da.CODE) AS TOTAL_FLOW, SUM(CASE WHEN da.DIRECTION = '1' THEN 1 ELSE 0 END) AS UPSTREAM_FLOW, SUM(CASE WHEN da.DIRECTION = '2' THEN 1 ELSE 0 END) AS DOWNSTREAM_FLOW, ROUND(100 * SUM(CASE WHEN da.DIRECTION = '1' THEN 1 ELSE 0 END) / COUNT(da.CODE), 2) || '%' AS UPSTREAM_PERCENTAGE, ROUND(100 * SUM(CASE WHEN da.DIRECTION = '2' THEN 1 ELSE 0 END) / COUNT(da.CODE), 2) || '%' AS DOWNSTREAM_PERCENTAGE FROM BAYONET_DYNAMIC.DATAFUSION da JOIN BAYONET_BASICS.SYS_BAYONET sb ON da.BAYONET_ID = sb.ID WHERE sb.NAME = '油墩港(东大盈船闸)' AND da.PASSTIME >= TO_DATE('2025-01-01', 'YYYY-MM-DD') AND da.PASSTIME < TO_DATE('2025-02-01', 'YYYY-MM-DD') GROUP BY EXTRACT(DAY FROM da.PASSTIME) ORDER BY DAY_OF_MONTH'''

    # question = '统计一月份各卡口点的船舶通过数量，按照通过船舶数量降序排列，只显示通过量前10的卡口。'
    # sql_result = [('黄浦江(松浦大桥南岸)', 24744), ('杭申线卡口', 14335), ('苏申内港线(上港宜东)', 6897), ('平申线省际检查站', 4634), ('大治河船闸', 4341),
    #               ('苏申外港线省际检查站', 3767), ('苏申内港线省际检查站', 3471), ('杨思船闸口', 2495), ('长湖申线卡口', 1797), ('油墩港(油墩港船闸)', 1749)]
    # generated_sql = '''SELECT * FROM ( SELECT b.NAME AS 卡口名称, COUNT(d.CODE) AS 通过船舶数量 FROM BAYONET_BASICS.SYS_BAYONET b JOIN BAYONET_DYNAMIC.DATAFUSION d ON b.ID = d.BAYONET_ID WHERE d.PASSTIME >= TO_DATE('2025-01-01', 'YYYY-MM-DD') AND d.PASSTIME < TO_DATE('2025-02-01', 'YYYY-MM-DD') GROUP BY b.NAME ORDER BY 通过船舶数量 DESC ) WHERE ROWNUM <= 10'''
    #
    # try:
    #
    #     # 可视化SQL结果
    #     output_file = visualize_sql_result(
    #         sql_result=sql_result,
    #         query=generated_sql,
    #         question=question
    #     )
    #
    #     if output_file:
    #         print(f"可视化已完成，请打开以下文件查看: {output_file}")
    #
    # except Exception as e:
    #     print(f"执行查询或可视化时出错: {e}")